﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Text;
using System.Data.Common;

/// <summary>
///TeamGroupCtrl 的摘要说明
/// </summary>
public class TeamGroupCtrl
{
	public TeamGroupCtrl()
	{
	}
    /// <summary>
    /// 根据条件获取团数据
    /// </summary>
    /// <param name="start"></param>
    /// <param name="end"></param>
    /// <param name="teamstate"></param>
    /// <param name="username">我的团（当前登录用户）</param>
    /// <param name="departmentId"></param>
    /// <returns></returns>
    public static DataTable GetTeamGroupData(DateTime start, DateTime end, TeamStateEnum teamstate, string teamname)
    {

        Database db = DatabaseFactory.CreateDatabase();
        StringBuilder szCondition = new StringBuilder();

        if (teamname != "")//我的团条件，根据当前登录人判定
            szCondition.AppendFormat(" TourgroupName like'%{0}%'", teamname);

        String Sql = "";
        if (teamstate == TeamStateEnum.全部)
        {
            Sql = string.Format("SELECT * FROM orgt_tourgroup WHERE TourgroupState <> '{0}'AND TourgroupState <> '{1}' AND StartDate >='{2}' AND StartDate<='{3}'", TeamStateEnum.删除, TeamStateEnum.取消, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));//convert(varchar(20),StartDate,120)
        }
        else
        {
            Sql = string.Format("SELECT * FROM orgt_tourgroup WHERE TourgroupState='{0}' AND StartDate>='{1}' AND StartDate<='{2}' ", teamstate, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));
        }


        if (szCondition.Length != 0)
            Sql += " AND " + szCondition.AppendFormat(" ORDER BY StartDate");
        else
            Sql += " ORDER BY StartDate";

        DbCommand dbCommand = db.GetSqlStringCommand(Sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }

    /// <summary>
    ///  根据给定的客户名称检索客户所在团信息
    /// </summary>
    /// <param name="guestName"></param>
    /// <returns></returns>
    public static DataTable GetTeamGroupData(string guestName)
    {
        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format("SELECT * FROM orgt_tourgroup WHERE tourgroupid in (SELECT tourgroupid FROM orgt_guestlist WHERE [Name] like '%'+@GuestName+'%')");
        dbCommand = db.GetSqlStringCommand(sql);
        db.AddParameter(dbCommand, "@GuestName", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, guestName);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }

    public static DataTable GetTeamGroupRowData(string tourGroupID)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = "SELECT * FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
        dbCommand = db.GetSqlStringCommand(sql);

        db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;

    }
    public static DataTable GetGuestListData(string tourGroupId)
    {
        Database db = DatabaseFactory.CreateDatabase();

        string Sql = string.Format("SELECT * FROM orgt_GuestList WHERE  TourgroupID ='{0}' ORDER BY OrderID", tourGroupId);

        DbCommand dbCommand = db.GetSqlStringCommand(Sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }

    public static DataSet GetTourGroupSchedule(string tourGroupID)
    {
        Database db = DatabaseFactory.CreateDatabase();
        string sql = string.Format("SELECT [TourGroupScheduleID], [TourgroupID], [Date], [Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType], [TravelAgencyID],[TravelAgencyName], [Reception], [Context], [Dining], [Hotel],[HotelID],[Seat],[ScheduleMethod] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'ORDER BY [Date], [Time]", tourGroupID);

        return db.ExecuteDataSet(CommandType.Text, sql);
    }
}